package com.base.service.impl;

/**
 *@Copyright:Copyright (c) 2014 - 2100
 *@Company:�????
 */

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.Set;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.report.ReportEnginer;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.base.callback.UrExcelCallBack;
import com.base.common.CommonConstants;
import com.base.service.UrExcelOptionService;
import com.base.util.JUtil;





/**
 *@Title:
 *@Description:
 *@Author:uwen
 *@Since:2015-3-13
 *@Version:1.1.0
 */
@Transactional
@Service("urExcelOptionService")
public class UrExcelOptionServiceImpl implements UrExcelOptionService {
	
	
	/**
	 * Excel文件出
	 */
    @SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
    public String excelOutNew(Map<String,Object> map,String templateFile,HttpServletRequest request,HttpServletResponse response) throws Exception {
        if (null == map) {
            return "导出失败!";
        }
        List<Map<String,Object>> list = (List<Map<String,Object>>)map.get("excelList");
        if (null == list) {
            return "导出失败!";
        }
       
        ReportEnginer enginer = new ReportEnginer();
        String path = request.getRealPath("") + templateFile;
        OutputStream os = null;
       
        String title = JUtil.getDateStr(new Date(), "yyyymmddhhMMss") + "-" + new Random().nextInt(500);
        response.reset();
        response.setContentType("application/msexcel");
        response.addHeader("Content-Disposition", "attachment;filename="
                + new String((title + ".xls").getBytes(), "ISO8859_1"));
        os = response.getOutputStream();
       
        enginer.excute(new FileInputStream(new File(path)), map, os);
        os.flush();
        os.close();
        return null;
    }
	

	/**
	 * Excel文件出
	 */
    @SuppressWarnings({ "rawtypes", "unchecked", "deprecation" })
    public String excelOut(List<?> list,String templateFile,Class myClass,HttpServletRequest request,HttpServletResponse response) throws Exception {
        if (null == list) {
            return "导出失败!";
        }
        ReportEnginer enginer = new ReportEnginer();
        String path = request.getRealPath("") + templateFile;
        OutputStream os = null;
       
        String title = JUtil.getDateStr(new Date(), "yyyymmddhhMMss") + "-" + new Random().nextInt(500);
        response.reset();
        response.setContentType("application/msexcel");
        response.addHeader("Content-Disposition", "attachment;filename="
                + new String((title + ".xls").getBytes(), "ISO8859_1"));
        os = response.getOutputStream();
        Map<String, Object> context = getContext(list,myClass);
        enginer.excute(new FileInputStream(new File(path)), context, os);
        os.flush();
        os.close();
        return null;
    }

    public String excelModelOut(String templateModelFile,HttpServletRequest request,HttpServletResponse response) throws Exception {
        // 下载模板文件
        String path = request.getRealPath("") + templateModelFile;
        OutputStream os = null;
        String title =  JUtil.getDateStr(new Date(), "yyyymmddhhMMss") + "-" + new Random().nextInt(500);
        response.reset();
        response.setContentType("application/msexcel");
        InputStream inStream = new FileInputStream(path);// 文件的存放路径
        // 设置输出的格式
        response.reset();
        response.setContentType("bin");
        response.addHeader("Content-Disposition", "attachment;filename="
                + new String((title + ".xls").getBytes(), "ISO8859_1"));
        // 循环取出流中的数据
        byte[] b = new byte[100];
        int len;
        try {
            while ((len = inStream.read(b)) > 0)
                response.getOutputStream().write(b, 0, len);
            inStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return null;
    }

    //addTime@yyyy-MM-dd;updateTime@yyyy-MM-dd HH:mm:ss
    @SuppressWarnings({ "rawtypes", "unchecked" })
    private Map<String, Object> getContext(List<Map<String,Object>> list) throws Exception {
        Map<String, Object> context = new HashMap<String, Object>();
        context.put("excelList", list);
        Map<String,Object> foot = new HashMap<String,Object>();
        foot.put("sumCapitalTotal", 1);
        foot.put("sumCapitalReal", 2);
        context.put("footer", foot);
        return context;
    }
    
    @SuppressWarnings({ "rawtypes", "unchecked" })
    private Map<String, Object> getContext(List<?> list,Class myClass) throws Exception {
        List<?> recordList = list;
        // 遍历对象属性
        Field[] fields = myClass.getDeclaredFields();
        String[] keys = new String[fields.length];
        int i = 0;
        for (i = 0; i < fields.length; i++) {
            keys[i] = fields[i].getName();
        }
        List<Map> excelList = new ArrayList<Map>();
        for (Object record : recordList) {
            Map excelMap = new HashMap();
            for (String fieldName : keys) {
                //String methodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                String methodName = JUtil.getGetMethod(fieldName);
               // Method method = myClass.getMethod(methodName);
                Method method = JUtil.getMethod(myClass, methodName);
                Object value = method.invoke(record);
                if (null != value && value.getClass() == Date.class) {
                    excelMap.put(fieldName, JUtil.getDateStr((Date) value, "yyyy-MM-dd HH:mm:ss"));
                    continue;
                }
                excelMap.put(fieldName, null != value ? value.toString() : "");
            }
            excelList.add(excelMap);
        }
        Map<String, Object> context = new HashMap<String, Object>();
        context.put("excelList", excelList);
        return context;
    }
	
	/**
	 *Excel文件导入
	 */
    @SuppressWarnings({ "rawtypes" })
	public  Map<String,Object> getObjectListFromExcel(File file, Class myclass,UrExcelCallBack callBack) throws Exception {
		return getObjectListFromExcel(getRegDataSys(file, 2), myclass,callBack);
	}
    
    /**
	 *Excel文件导入
	 *spring mvc
	 */
    @SuppressWarnings({ "rawtypes" })
	public  Map<String,Object> getObjectListFromExcel(InputStream is, Class myclass,UrExcelCallBack callBack) throws Exception {
		return getObjectListFromExcel(getRegDataSys(is, 2), myclass,callBack);
	}
	// 此方法可以处理批量导入
	@SuppressWarnings({ "rawtypes", "unchecked" })
	private  Map<String,Object> getObjectListFromExcel(List<String[]> excerlList, Class myclass,UrExcelCallBack callBack) throws Exception {
		//Set list = new HashSet();
		Map<String,Object> result = new HashMap<String,Object>();
		int i = 0;
		String[] myConfig = excerlList.get(0);// 第一行获取类配置信息
		Method[] fieldMethod = new Method[myConfig.length];
		Class[] fieldTypes = new Class[myConfig.length];
		
		for (String str : myConfig) {
			String[] tmpStr = str.split(":");
			String tmpFieldName = tmpStr[0];
			String tmpFieldType = tmpStr[1];
			Method theMethod = JUtil.getMethod(myclass, JUtil.setGetMethod(tmpFieldName), Class.forName(tmpFieldType));
			fieldMethod[i] = theMethod;
			fieldTypes[i] = Class.forName(tmpFieldType);
			i++;
		}
		
		callBack.callBack(excerlList,result,fieldMethod,fieldTypes,this);//回调action的方法
		if(result.containsKey(CommonConstants.INPUT_RESULTKEY)){
			Object objList = result.get(CommonConstants.INPUT_RESULTKEY);
			if(null!=objList){
				return result;
			}
		}
		
		Set list = new HashSet();
		for (i = excerlList.size() - 1; i >= 1; i--) {
			String[] arr = excerlList.get(i);
			int j;
			Object obj = myclass.newInstance();
			for (j = arr.length - 1; j >= 0; j--) {
				Class theClass = fieldTypes[j];
				String value = arr[j];
				Object realValue = getCurrentTypeValue(theClass, value);
				Method m = fieldMethod[j];
				m.invoke(obj, realValue);// 执行set方法
			}
			list.add(obj);
		}
		result.put(CommonConstants.INPUT_RESULTKEY, list);
		return result;
	}
	
	 @SuppressWarnings({ "rawtypes"})
	   public  Object getCurrentTypeValue(Class currentType, String value) throws ParseException {
		if (currentType == Integer.class) {
			return Integer.parseInt(value);
		}
		if (currentType == Double.class) {
			return Double.parseDouble(value);
		}
		if (currentType == Float.class) {
			return Float.parseFloat(value);
		}
		if (currentType == Date.class) {
			System.out.println(value);
			return new SimpleDateFormat("yyyy-mm-dd").parse(value);
		}
		if (currentType == Long.class) {
			return Long.parseLong(value);
		}
		return value;
	}
	 
	 /**
		 * @Title: getRegDataSys
		 * @Description: 获取系统数据
		 * @param InputStream
		 * @param index
		 * @return
		 * @throws Exception
		 * @throws IOException
		 * @return List<String[]> 返回类型
		 * @throws
		 */
		public  List<String[]> getRegDataSys(InputStream is, int index) throws Exception, IOException {

			String[][] result = getData(is, index);
			List<String[]> result1 = new ArrayList<String[]>();
			int rowLength = result.length;
			for (int i = 0; i < rowLength; i++) {
				String[] arr = null;
				arr = new String[result[i].length];
				for (int j = 0; j < result[i].length; j++) {
					arr[j] = result[i][j];
				}
				result1.add(arr);
			}

			return result1;
		}

	/**
	 * @Title: getRegDataSys
	 * @Description: 获取系统数据
	 * @param file
	 * @param index
	 * @return
	 * @throws Exception
	 * @throws IOException
	 * @return List<String[]> 返回类型
	 * @throws
	 */
	public  List<String[]> getRegDataSys(File file, int index) throws Exception, IOException {

		String[][] result = getData(file, index);
		List<String[]> result1 = new ArrayList<String[]>();
		int rowLength = result.length;
		for (int i = 0; i < rowLength; i++) {
			String[] arr = null;
			arr = new String[result[i].length];
			for (int j = 0; j < result[i].length; j++) {
				arr[j] = result[i][j];
			}
			result1.add(arr);
		}

		return result1;
	}

	/**
	 * @Title: getRegDataSys
	 * @Description: 获取系统数据
	 * @param file
	 * @param index
	 * @return
	 * @throws Exception
	 * @throws IOException
	 * @return List<String[]> 返回类型
	 * @throws
	 */
	public List<String[]> getRegDataSys(String filepath, int index) throws Exception, IOException {
		return getRegDataSys(new File(filepath), index);
	}
	
	/**
	 * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
	 * @param file  读取数据的源Excel
	 * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
	 * @return 读出的Excel中数据的内容
	 * @throws FileNotFoundException
	 * @throws IOException
	 */

	public  String[][] getData(InputStream is, int ignoreRows)throws FileNotFoundException, IOException {
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		BufferedInputStream in = new BufferedInputStream(is);
		// 打开HSSFWorkbook
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell = null;

		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum();
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				
				for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;

						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								if (date != null) {
									value = new SimpleDateFormat("yyyy-MM-dd").format(date);
								} else {
									value = "";
								}

							} else {
								value = new DecimalFormat("0").format(cell.getNumericCellValue());
							}
							break;

						case HSSFCell.CELL_TYPE_FORMULA:
							if (!cell.getStringCellValue().equals("")) {
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue() + "";
							}
							break;

						case HSSFCell.CELL_TYPE_BLANK:
							break;

						case HSSFCell.CELL_TYPE_ERROR:
							value = "";
							break;

						case HSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y": "N");
							break;

						default:
							value = "";
						}
					}

					if (columnIndex == 0 && value.trim().equals("")) {
						break;
					}

					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					result.add(values);
				}
			}
		}

		in.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = result.get(i);
		}
		return returnArray;
	}


	/**
	 * 
	 * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
	 * 
	 * @param file
	 *            读取数据的源Excel
	 * 
	 * @param ignoreRows
	 *            读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
	 * 
	 * @return 读出的Excel中数据的内容
	 * 
	 * @throws FileNotFoundException
	 * 
	 * @throws IOException
	 */

	public  String[][] getData(File file, int ignoreRows)throws FileNotFoundException, IOException {
		List<String[]> result = new ArrayList<String[]>();
		int rowSize = 0;
		BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));

		// 打开HSSFWorkbook
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		HSSFCell cell = null;

		for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
			HSSFSheet st = wb.getSheetAt(sheetIndex);
			// 第一行为标题，不取
			for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
				HSSFRow row = st.getRow(rowIndex);
				if (row == null) {
					continue;
				}
				int tempRowSize = row.getLastCellNum();
				if (tempRowSize > rowSize) {
					rowSize = tempRowSize;
				}
				String[] values = new String[rowSize];
				Arrays.fill(values, "");
				boolean hasValue = false;
				
				for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
					String value = "";
					cell = row.getCell(columnIndex);
					if (cell != null) {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_STRING:
							value = cell.getStringCellValue();
							break;

						case HSSFCell.CELL_TYPE_NUMERIC:
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								Date date = cell.getDateCellValue();
								if (date != null) {
									value = new SimpleDateFormat("yyyy-MM-dd").format(date);
								} else {
									value = "";
								}

							} else {
								value = new DecimalFormat("0").format(cell.getNumericCellValue());
							}
							break;

						case HSSFCell.CELL_TYPE_FORMULA:
							if (!cell.getStringCellValue().equals("")) {
								value = cell.getStringCellValue();
							} else {
								value = cell.getNumericCellValue() + "";
							}
							break;

						case HSSFCell.CELL_TYPE_BLANK:
							break;

						case HSSFCell.CELL_TYPE_ERROR:
							value = "";
							break;

						case HSSFCell.CELL_TYPE_BOOLEAN:
							value = (cell.getBooleanCellValue() == true ? "Y": "N");
							break;

						default:
							value = "";
						}
					}

					if (columnIndex == 0 && value.trim().equals("")) {
						break;
					}

					values[columnIndex] = rightTrim(value);
					hasValue = true;
				}
				if (hasValue) {
					result.add(values);
				}
			}
		}

		in.close();
		String[][] returnArray = new String[result.size()][rowSize];
		for (int i = 0; i < returnArray.length; i++) {
			returnArray[i] = result.get(i);
		}
		return returnArray;
	}

	/**
	 * 去掉字符串右边的空格
	 * @param str  要处理的字符串
	 * @return 处理后的字符串
	 */
	public static String rightTrim(String str) {
		if (str == null) {
			return "";
		}
		int length = str.length();
		for (int i = length - 1; i >= 0; i--) {
			if (str.charAt(i) != 0x20) {
				break;
			}
			length--;
		}

		return str.substring(0, length);

	}
}
